package hardwareToSQL;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

import hardwareToSQL.pruningAlgorithm.*;

import java.util.ArrayList;
import java.util.Properties;
import java.util.Hashtable;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;

import java.util.Date;


public class InsertSQL extends Thread{

	/**Property file holding info about how to create the database */
	private Properties databaseInfo;	
	
	/**Property file used to tell which name of a data String goes to
	 * which table */
	private Properties dataToTables;
	/**Hash table that maps the column name to the table name */
	private Hashtable<String, String> columnNameToTable;
	/**Array of all table names */
	private String[] tableNames;
	
	/**Hashtable storing the address of each created row */
	private Hashtable<String, RowRecord> rowStorage;
	
	/**Statement that will be used to insert the rows into the SQL */
	private Statement statement;
	/**The url to connect to the database */
	private String url;

	public InsertSQL() {

		init();
		
		/**get the current time, and add 100 milliseconds to it */
		Long now = (new Date()).getTime();
		/**Round the time to the nearest lower second */
		now = Math.round(now.doubleValue()/1000)*1000; 
    	now += 2000;                    
		
		Date waitUntil = new Date(now);
		for(RowRecord row:rowStorage.values()){
			row.startTimerAt(waitUntil, statement);
		}
	}
	
	
	
	/**Initialize the variables and class objects */
	private void init(){
		/**Establish the connection */
		statement = initializeDatabase();
		if ((statement) == null){
			/**If failed to connect, quit the system */
			System.err.print("Initialization failed.");
			System.exit(1);
		}
		/**Load the property files */
		try {
			databaseInfo = new Properties();
			
			dataToTables = new Properties();


			databaseInfo.load(new FileInputStream
					("properties/databaseInfo.properties"));
			
			dataToTables.load(new FileInputStream
					("properties/dataToTables.properties"));
		} catch (FileNotFoundException e) {
			System.err.print("\nFailed to load property file\n");
			System.exit(1);
		} catch (IOException e) {
			System.err.print("\nFailed to load property file\n");
			System.exit(1);
		} catch (NullPointerException e) {
			System.err.print("\nFailed to load property file\n");
			System.exit(1);
		}
	
		/**Retrieve the list of all table names */
		String temp = dataToTables.getProperty("TableNames");
		tableNames = temp.split(",");
		System.out.println(tableNames.toString());
		columnNameToTable = new Hashtable();
		for (int i=0;i<tableNames.length;i++){
			/**Take out all the white spaces in the name */
			tableNames[i] = tableNames[i].replaceAll("\\s", "");
			
			/**Initialize the hashtable columnNameToTable */
			String[] columnNames = dataToTables.
				getProperty(tableNames[i]).split(",");
			
			for(int k=0;k<columnNames.length;k++){
				columnNames[k] = columnNames[k].replaceAll("\\s", "");
				columnNameToTable.put(columnNames[k], tableNames[i]);
			}
		}
		/**Create the rows that will hold the groups of string */
		rowStorage = new Hashtable<String, RowRecord>();
		
		for(String names:tableNames){
			/**System.out.printf("<%s>\n", names); */
			rowStorage.put(names, 
					new RowRecord(
							names, null, 
					Integer.parseInt(dataToTables.getProperty("Time" + names))
					             )
					);
		}

		//Create the tables that will receive each row
		//prepareTables();

	}

	/**
	 * Register database driver, establish connection, and return the 
	 * corresponding statement object. @return a statement corresponding to 
	 * the connection. Null if the connection failed.
	 */
	private Statement initializeDatabase(){
		try{
			/**Initialize properties */
			databaseInfo = new Properties();
			databaseInfo.load(new FileInputStream
					("properties/databaseInfo.properties"));
		}catch (FileNotFoundException e){
			e.printStackTrace();
			System.err.print("\nProperties file for creating database + " +
					"not found\n");
		}catch (IOException e){
			e.printStackTrace();
			System.err.print("\nProperties file for creating database + " +
					"not found\n");
		}

		try {
			/**Register the driver class */
			Class.forName(databaseInfo.getProperty("DatabaseDriverClass"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.err.println("You need to install the specified database " +
			"driver before running this software.");
			System.exit(1);
		}
		
		//For example, url looks like "jdbc:mysql://localhost:3306/mysql"
		
		Connection con;
		try {//Try to connect to the database using the provided information 
			//With the schema set to what is provided. Let's say it's 
			//"bluesky"
			url = "jdbc:mysql://"+databaseInfo.getProperty("DatabaseAddress")
				+":"+databaseInfo.getProperty("DatabasePort")+"/"+
					databaseInfo.getProperty("Schema");
			con = (Connection) DriverManager.getConnection(url,
					databaseInfo.getProperty("User"), 
					databaseInfo.getProperty("Password"));
		} catch (SQLException e) {
			/**If the connection failed, than try to connect without a default 
			 * schema */
			System.out.println("Database: " + databaseInfo.getProperty("Schema") + 
					" does not exist.\nCreating new scheme...");
			url = "jdbc:mysql://"+databaseInfo.getProperty("DatabaseAddress")
				+":"+databaseInfo.getProperty("DatabasePort");
			try{
				con = (Connection) DriverManager.getConnection(url,
					databaseInfo.getProperty("User"), 
					databaseInfo.getProperty("Password"));
			}catch (SQLException d){
				/**Now it really goes wrong. Exit the system */
				d.printStackTrace();
				return null;
			}
		}
		System.out.println("Database Connection established.");
		System.out.println("URL: " + url);
		System.out.println("Connection: " + con);
		/**Try to initialize the statement */
		try {
			return (Statement) con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}

	/**Figure out which row this data object is to go to
	 * and send it to the corresponding row
	 * Do nothing if the SensorData object is default.
	 */ 
	protected void takeNextString(SensorData newSensorData){
		
		try{
			String tableName = columnNameToTable.get(newSensorData.getName());
			RowRecord row = rowStorage.get(tableName);
			row.updateRow(newSensorData);
		}
		catch (NullPointerException e){
			System.out.println("InsertSQL: Invalid data: " + newSensorData.getName());		
		}
	}
	
	/**
	 * Read from the bluesky.sql file and executes the sql statements in it.
	 * @param args
	 */
	public static void main(String[]args){

		InsertSQL database = new InsertSQL();
		String path = "SQL/bluesky.sql";

		BufferedReader queriesReader = null;
		try {//If the file is not found, exit.
			queriesReader = new BufferedReader(new FileReader(path));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			System.err.format("File %s is not found", path);
			System.exit(1);
		}
		//The list that contains the queries from the file.
		ArrayList<String> queries = new ArrayList<String>();
		try {
			String temp = "";
			while (queriesReader.ready()){
				//read the queries from the file.
				//If the query is complete (i.e. contains ";"),
				//then add it to the ArrayList
				String line = queriesReader.readLine();

				line = line.replaceAll("#.*", "");

				temp = temp.concat(line);
				if (temp.contains(";")){
					queries.add(temp);
					temp = "";
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		for (String query:queries){
			try{
				//ResultSet rs;
				int updateCount;
				System.out.format("Executing query: %s\n", query);
				if (database.statement.execute(query)){
					//If it's a statement returns a ResultSet
					//Although, it should not happen.
					//The file should not contain any SELECT or LIST 
					//TABLE statements.
					//rs = database.statement.getResultSet();
				}else{
					//If a statement is a UPDATE, ALTER, DELETE or CREATE 
					//statement Which returns a update count
					updateCount = database.statement.getUpdateCount();
					System.out.format("%d rows have been updated\n", 
							updateCount);
				}
			}catch (SQLException e){
				System.err.format("SQL statement\n'%s'\nfailed to " +
						"execute.\n", query);
				System.err.println(e);
			}catch (NullPointerException e){
				System.err.format("SQL statement\n'%s'\nfailed to " + 
						"execute.\n", query);
				System.err.println(e);
			}
		}
	}
	
	public Hashtable<String,Integer> timesRowUpdated(){
		Hashtable<String,Integer> result = new Hashtable<String,Integer>();
		for(RowRecord row:rowStorage.values()){
			result.put(row.getName(), row.timesUpdated);
		}
		return result;
	}
}
